"""
SQL建表语句
drop table if exists tb_contants;
use hrs;

create table `tb_contants` (
`con_id` int auto_increment not null comment '序号',
`con_name` varchar(4) not null comment '姓名',
`con_sex` char(1) default '男' comment '性别',
`con_tel` varchar(100) comment'电话号码',
primary key (`con_id`)
)engine = InnoDB ;
"""
import functools
import MySQLdb

create_connect = functools.partial(
       MySQLdb.connect, host='localhost', port=3306,
                       user='guest', password='123456',
                       charset='utf8mb4', autocommit=False)
conn = create_connect(database='hrs')


# 1.写入联系人
def write_sql(con_name, con_sex, con_tel):
    try:
        with conn.cursor() as cursor:
            row = cursor.execute(
                'insert into `tb_contants`(`con_name`,`con_sex`,`con_tel`)'
                'values(%s, %s, %s)',
                (con_name, con_sex, con_tel),
            )
            if row == 1:
                print('新建联系人完成')
            else:
                pass
        conn.commit()
    except MySQLdb.MySQLError as err:
        print(err)
        conn.rollback()
    finally:
        conn.close()
    choose()


# 2.查找联系人
def find_contact(con_name):
    try:
        with conn.cursor(MySQLdb.cursors.DictCursor) as cursor:
            cursor.execute('select `con_id`, `con_name`,`con_sex`,`con_tel` from `tb_contants` where `con_name`=%s',
                           (con_name,))
            row_dict = cursor.fetchone()
            return row_dict
    except MySQLdb.MySQLError as err:
        print(err)
    finally:
        conn.close()
    choose()


# 3.修改联系人信息
def update_contact(con_id, con_name, con_sex, con_tel):
    try:
        with conn.cursor() as cursor:
            row = cursor.execute('update`tb_contants` set `con_name`=%s,`con_sex`=%s,`con_tel`=%s where con_id = %s',
                                 (con_id, con_name, con_sex, con_tel))
            if row == 1:
                print('修改联系人成功')
        conn.commit()
    except MySQLdb.MySQLError as err:
        print(err)
        conn.rollback()
    finally:
        conn.close()
    choose()


# 4.删除联系人
def delete_con(con_name):
    con_id = find_contact(con_name)["con_id"]
    conn = create_connect(database='hrs')
    try:
        with conn.cursor() as cursor:
            affected_rows = cursor.execute('delete from  `tb_contants` where con_id = %s',
                                           (con_id,)
                                           )
            if affected_rows == 1:
                print('删除成功')
                conn.commit()
    except MySQLdb.MySQLError as err:
        print(err)
        conn.rollback()
    finally:
        conn.close()
    choose()


# 5.退出程序
def close():
    print('已退出通讯录')


def choose():
    choose_list = ['1.新建联系人', '2.查找联系人', '3.修改联系人信息', '4.删除联系人', '5.退出程序']
    print('菜单')
    for x in choose_list:
        print(x)

    choose = eval(input('请输入指令数字:'))
    # 选择序号
    if choose == 1:
        con_name = input('请输入姓名：')
        con_sex = input('请输入性别：')
        con_tel = input('请输入电话号码')
        write_sql(con_name, con_sex, con_tel)
    elif choose == 2:
        con_name = input('请输入联系人姓名')
        con = find_contact(con_name)
        print(f'联系人姓名：{con["con_name"]}')
        print(f'联系人性别：{con["con_sex"]}')
        print(f'联系人电话：{con["con_tel"]}')
    elif choose == 3:
        con_name = input('请输入需要修改的联系人姓名')
        modify = find_contact(con_name)
        con_name = modify["con_name"]
        con_sex = modify["con_sex"]
        con_tel = modify["con_tel"]
        con_id = modify["con_id"]
        print(modify)
        up_name = input('请输入姓名:')
        up_company = input('请输入性别：')
        up_tel = input('请输入联系电话：')
        update_contact(up_name, up_company, up_tel, con_id)
    elif choose == 4:
        con_name = input('请输入需要删除的联系人姓名')
        delete_con(con_name)

choose()


